package com.gemptc.yueban.daoImp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.gemptc.bean.RegistrationBean;
import com.gemptc.daoimp.SelectImp;
import com.gemptc.utils.DBUtil;
import com.gemptc.yueban.dao.ISelectRegistration;

public class SelectRegistrationImp implements ISelectRegistration {

	@Override
	public List<RegistrationBean> getRegistrationBean(int ac_Id) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<RegistrationBean> registrationlist = new ArrayList<RegistrationBean>();
		String sql = "select* from registration_list  where ac_id=? ";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, ac_Id);
			// 查找到数据并 添加
			resultSet = preparedStatement.executeQuery();
			// 遍历循环并得到数组list里的数据
			while (resultSet.next()) {
				// 查找到表registration_list里的第1列数据整数类型的 rl_id
				int rl_id = resultSet.getInt(1);
				// 查找到表registration_list里的第2列数据整数类型的user_id
				int user_id = resultSet.getInt(2);
				SelectImp img = new SelectImp();
				String user_imgPath = img.select1(user_id);
				// 用SelectUserName方法通过 activity里user_id查找user表里的user_id,
				// 查找到user表里的user_nickname，
				SelectImp nickname = new SelectImp();
				String user_nickname = nickname.select(user_id);
				// 查找到表registration_list里的第3列数据整数类型的ac_id
				int ac_id = resultSet.getInt(3);
				// 查找到表registration_list里的第4列数据整数类型的rl_number
				int  rl_number = resultSet.getInt(4);
				SelectRegistrationImp sum=new SelectRegistrationImp();
				int rl_num_sum=sum.getSum(ac_id);
				// 查找到表registration_list里的第5列数据整数类型的rl_phone
				String rl_phone = resultSet.getString(5);
				// 查找到表registration_list里的第6列数据字符串类型的rl_Message
				String rl_Message = resultSet.getString(6);
				RegistrationBean registrationBean = new RegistrationBean(
						rl_id, user_id, ac_id, rl_number,
						rl_num_sum, user_imgPath, user_nickname,
						rl_Message, rl_phone);
				registrationlist.add(registrationBean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return registrationlist;
	}

	@Override
	public int getSum(int ac_Id) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int rl_num_sum = 0;                                    
		String sql = "SELECT SUM(rl_number) AS rl_num_sum FROM registration_list  where ac_id=? ";
		// 查找到数据并 添加
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, ac_Id);
			// 查找到数据并 添加
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				rl_num_sum = resultSet.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return rl_num_sum;
	}
}
